import openpyxl
import pymysql
# import random
# # a = openpyxl.Workbook('原神.xlsx')
# # a.create_sheet(title='哥伦比亚')
# # a.save('原神.xlsx')
# data = []
# for i in range(1,2001):
#     id = i
#     idcard = ''.join(random.choices('1234567890',k=8))
#     username = ''.join(random.choices('甲乙丙丁木白石椰',k=3))
#     realname = ''.join(random.choices('旅行者马薇卡芙宁娜',k=2))
#     pwd = ''.join(random.choices('1234567890',k=4))
#     telephone =''.join(random.choices('1234567890',k=11))
#     email = ''.join(random.choices('123456qcom@',k=6))
#     age = random.randint(1,99)
#     sex = ''.join(random.choices('男女',k=1))
#     address = ''.join(random.choices(['璃月','须弥','诺德卡来'],k=1))
#     data.append([id,idcard,username,realname,pwd,telephone,email,age,sex,address])
b = openpyxl.load_workbook('原神.xlsx')
c = b['哥伦比亚']
# for j in data:
#     c.append(j)
#b.save('原神.xlsx')
class ReadExcel:
    @staticmethod
    def read_excel(file_name,sheet_name):
        a = openpyxl.load_workbook(file_name)
        b = a[sheet_name]
        data =[[c.cell(row=i,column=j).value for j in range(1,b.max_column+1)]for i in range(1,b.max_row+1)]
        return data
if __name__ == '__main__':
    a = pymysql.connect(host='localhost',user='root',passwd='1234',database='jiangsu',port=3306,charset='utf8')
    B=a.cursor()
    B.execute('create table if not exists 哥伦比亚(id int(11),idcard varchar(50),'
              'username varchar(50),realname varchar(50),pwd varchar(50),'
              'telephone varchar(12),email varchar(100),age int(11),sex varchar(20),'
              'address varchar(200),hiredate date,sal double(9,2),job varchar(100),company varchar(100),city varchar(100)) ')
    data = ReadExcel.read_excel('原神.xlsx','哥伦比亚')
    data_tuple = [tuple(i)for i in data]
    B.executemany('insert into 哥伦比亚(id,idcard,username,realname,pwd,telephone,email,age,sex,address) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ',data_tuple)
    a.commit()
    print('提交成功')








